*This program downloads GDELT data from Google BigQuery


* This downloads global monthly GDELT data
SELECT DISTINCT MonthYear,Actor1CountryCode, Actor2CountryCode, 
                -sum(GoldsteinScale) as tension, -sum(GoldsteinScale*NumArticles) as tension_weighted,
                count(GLOBALEVENTID) as NumEvents, sum(NumArticles) as NumEvents_weighted,
                -sum(CASE WHEN GoldsteinScale < 0 THEN GoldsteinScale ELSE 0 END) as conflict,
                -sum(CASE WHEN GoldsteinScale < 0 THEN GoldsteinScale*NumArticles ELSE 0 END) as conflict_weighted,
                sum(CASE WHEN GoldsteinScale < 0 THEN 1 ELSE 0 END) as NumNegEvents, 
                sum(CASE WHEN GoldsteinScale < 0 THEN NumArticles ELSE 0 END) as NumNegEvents_weighted,
                sum(CASE WHEN GoldsteinScale > 0 THEN GoldsteinScale ELSE 0 END) as coop,
                sum(CASE WHEN GoldsteinScale > 0 THEN GoldsteinScale*NumArticles ELSE 0 END) as coop_weighted,
                sum(CASE WHEN GoldsteinScale > 0 THEN 1 ELSE 0 END) as NumPosEvents, 
                sum(CASE WHEN GoldsteinScale > 0 THEN NumArticles ELSE 0 END) as NumPosEvents_weighted,
                -avg(AvgTone) as negTone, -sum(AvgTone*NumArticles)/sum(NumArticles) as negTone_weighted
FROM gdelt-bq.full.events
WHERE Year >= 2000 AND Year <=2023
AND Actor1CountryCode != Actor2CountryCode
GROUP BY Actor1CountryCode, Actor2CountryCode, MonthYear


* This downloads daily China-foreign GDELT data
SELECT DISTINCT SQLDATE, Actor1CountryCode, Actor2CountryCode, 
                -sum(GoldsteinScale) as tension, -sum(GoldsteinScale*NumArticles) as tension_weighted,
                count(GLOBALEVENTID) as NumEvents, sum(NumArticles) as NumEvents_weighted,
                -sum(CASE WHEN GoldsteinScale < 0 THEN GoldsteinScale ELSE 0 END) as conflict,
                -sum(CASE WHEN GoldsteinScale < 0 THEN GoldsteinScale*NumArticles ELSE 0 END) as conflict_weighted,
                sum(CASE WHEN GoldsteinScale < 0 THEN 1 ELSE 0 END) as NumNegEvents, 
                sum(CASE WHEN GoldsteinScale < 0 THEN NumArticles ELSE 0 END) as NumNegEvents_weighted,
                sum(CASE WHEN GoldsteinScale > 0 THEN GoldsteinScale ELSE 0 END) as coop,
                sum(CASE WHEN GoldsteinScale > 0 THEN GoldsteinScale*NumArticles ELSE 0 END) as coop_weighted,
                sum(CASE WHEN GoldsteinScale > 0 THEN 1 ELSE 0 END) as NumPosEvents, 
                sum(CASE WHEN GoldsteinScale > 0 THEN NumArticles ELSE 0 END) as NumPosEvents_weighted,
                -avg(AvgTone) as negTone, -sum(AvgTone*NumArticles)/sum(NumArticles) as negTone_weighted                
FROM gdelt-bq.full.events
WHERE Year >= 2000 AND Year <=2023
AND Actor1CountryCode != Actor2CountryCode
AND (Actor1CountryCode = "CHN" OR Actor2CountryCode = "CHN")
GROUP BY Actor1CountryCode, Actor2CountryCode, SQLDATE